<?php require_once('../Connections/tecno.php'); ?>
<?php
define('FPDF_FONTPATH','font/');
require('../fpdf16/mysql_table.php');
session_start();

$tpCli = $_REQUEST['tipoCli'];
$tpCli = (int)$tpCli;
if($tpCli==1){$text=" del Ministerio";}else{$text=" Particulares";}
$giros = $_REQUEST['giro'];
$today=date('Y-m-d');
$imp = $_REQUEST['chk'];
//$today=date('2011-12-01');

class PDF extends PDF_MySQL_Table{	
	function Header(){	
		if(isset($imp)){
			//Title
			$this->Image('../images/logo.jpg',5,5, 50);
			$this->SetFont('Arial','B',7);
			$this->Cell(130,3,'',0,0,'C');
			$this->Cell(23,3,utf8_decode('Dirección Fiscal: '),0,0,'L');	
			$this->SetFont('Arial','',7);
			$this->Cell(80,3,utf8_decode('Av. Bolívar C.C. Amelia Piso 3 Ofic. 306'),0,0,'L');
			$this->SetFont('Arial','I',8);
			$this->Cell(0,3,'Impreso el: '. date('d/m/Y'),0,1,'R');
		//Linea 4
			$this->SetFont('Arial','',7);
			$this->SetTextColor(00,00,00);
			$this->Cell(124,3,'',0,0,'C');
			$this->Cell(80,3,'Cagua Edo. Aragua. Telfs: 0244-3964557 / 0426-5125749',0,1,'C');
		
		//Linea 5
			$this->Cell(125,3,'',0,0,'C');
			$this->Cell(80,3,'0416-0458957 / 0426-5180385 / 0424-3261656',0,1,'C');
			$this->Ln(10);
			//Ensure table header is output
		}else{
			//Title
			$this->Image('../images/logo.jpg',5,5, 50);
			$this->SetFont('Arial','B',7);
			$this->Cell(65,3,'',0,0,'C');
			$this->Cell(23,3,utf8_decode('Dirección Fiscal: '),0,0,'L');	
			$this->SetFont('Arial','',7);
			$this->Cell(80,3,utf8_decode('Av. Bolívar C.C. Amelia Piso 3 Ofic. 306'),0,0,'L');
			$this->SetFont('Arial','I',8);
			$this->Cell(0,3,'Impreso el: '. date('d/m/Y'),0,1,'R');
		//Linea 4
			$this->SetFont('Arial','',7);
			$this->SetTextColor(00,00,00);
			$this->Cell(59,3,'',0,0,'C');
			$this->Cell(80,3,'Cagua Edo. Aragua. Telfs: 0244-3964557 / 0426-5125749',0,1,'C');
		
		//Linea 5
			$this->Cell(59,3,'',0,0,'C');
			$this->Cell(80,3,'0416-0458957 / 0426-5180385 / 0424-3261656',0,1,'C');
			$this->Ln(10);
		}
		parent::Header();
	}
}
if(isset($imp)){
	$pdf=new PDF('L','mm','Legal');
}else{
	$pdf=new PDF('P','mm','Letter');
}
$pdf->SetLeftMargin(6);
$pdf->SetRightMargin(7);
$pdf->Open();
$pdf->AddPage();

//T�tulo del Reporte
$pdf->Ln(3);
$pdf->SetFont('times','B',12); 
$pdf->Cell(1,4,'Clientes'.$text,0,1);
$pdf->Cell(1,10,"Listado de Morosos Productos con". $giros .utf8_decode(" o más cuotas vencidas"),0,1,'L');
$pdf->SetFont('Arial','',10);

//Connect to database
mysql_select_db($database_tecno, $tecno);

$queryCliente = "SELECT *, COUNT(giro) as giro, CONCAT(nombres,' ', primerApellido) as fullName, CONCAT(telefono,' / ',celular) as telefonos FROM datospersonales, detallescliente, refpersonal, inflaboral WHERE status<>1 AND fechaV <'$today' AND datospersonales.id = detallescliente.id  AND datospersonales.id = refpersonal.id AND datospersonales.id=inflaboral.id AND inflaboral.ministerio= '$tpCli' AND detallescliente.tipo = 1 GROUP BY detallescliente.idArt HAVING COUNT(giro) >= '$giros' ORDER BY datospersonales.nombres ASC";
$cliente = mysql_query($queryCliente, $tecno) or die(mysql_error());
$rowCliente = mysql_fetch_assoc($cliente);
$totalRowsCliente = mysql_num_rows($cliente);

if ($totalRowsCliente >0){

	//Titulos Tabla
	$pdf->SetFont('arial','B',8); 
	$pdf->SetDrawColor(0);
	$pdf->SetFillColor(235,235,235);
	$pdf->SetTextColor(0);
	$pdf->SetLineWidth(0.15);
if(isset($imp)){	
	$pdf->Cell(48,6,'Nombres',1,0,'C',true);
	$pdf->Cell(38,6,'Tel�fonos',1,0,'C',true);
	$pdf->Cell(78,6,'Direcci�n',1,0,'C',true);
	$pdf->Cell(35,6,'Referencia',1,0,'C',true);	
	$pdf->Cell(20,6,'Tel�fono',1,0,'C',true);	
	$pdf->Cell(43,6,'Producto',1,0,'C',true);
    $pdf->Cell(20,6,'Cuotas Pend.',1,0,'C',true); 
	$pdf->Cell(17,6,'Monto Tot.',1,0,'C',true);
	$pdf->Cell(17,6,'Pagado',1,0,'C',true);
	$pdf->Cell(17,6,'Debe',1,1,'C',true);
}else{
	$pdf->Cell(48,6,'Nombres',1,0,'C',true);
	$pdf->Cell(43,6,'Producto',1,0,'C',true);
    $pdf->Cell(20,6,'Cuotas Pend.',1,0,'C',true); 
	$pdf->Cell(17,6,'Monto Tot.',1,0,'C',true);
	$pdf->Cell(17,6,'Pagado',1,0,'C',true);
	$pdf->Cell(17,6,'Debe',1,1,'C',true);
}	
	$pdf->Ln(1);

	$color=array("235,235,235","255,255,255");
	$conta_color=0;
	$acumTotArt=0;
	$acumPag=0;
	$acumDebe=0;
	//table/**/
	do {				
		$idArt=$rowCliente['idArt'];

		$queryPagos = "SELECT *, SUM(monto) as totalArt  FROM  detallescliente WHERE detallescliente.idArt = '$idArt'";
		$pagos = mysql_query($queryPagos, $tecno);
		$rowPagos = mysql_fetch_assoc($pagos);
		$totalRowsPagos = mysql_num_rows($pagos);
		//acumulamos
		$acumTotArt = round($rowPagos['totalArt'] + $acumTotArt,2);
		
		$totalArticulo = "SELECT Sum(cantidad) as pagado FROM pagos WHERE idArt = '$idArt' AND status = 0";
		$tot = mysql_query($totalArticulo, $tecno) or die(mysql_error());
		$row_tot = mysql_fetch_assoc($tot);
		$totalRows_tot = mysql_num_rows($tot);
		//acumulamos
		$acumPag = round($row_tot['pagado'] + $acumPag,2);

		$total = $rowPagos['totalArt'];
		$pagado = $row_tot['pagado'];
		$total = round($total,2);
		$pagado = round($pagado,2);
		$debe = ($total - $pagado);
		$acumDebe = $debe + $acumDebe;
		
		$conta_color++;
		$fill= $color[$conta_color%2];
		$pdf->SetFillColor($fill);
		$pdf->SetFont('arial','',8); 
if(isset($imp)){		
		$pdf->Cell(48,5,substr($rowCliente['fullName'],0,50),0,0,'L',true);
		$pdf->Cell(38,5,$rowCliente['telefonos'],0,0,'L',true);	
		$pdf->Cell(78,5,$rowCliente['direccionHab'],0,0,'L',true);
		$pdf->Cell(35,5,$rowCliente['nombres1'],0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['telefono1'],0,0,'L',true);
		$pdf->Cell(43,5,substr($rowCliente['productoDes'],0,43),0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['giro'],0,0,'C',true);
		$pdf->Cell(17,5,number_format($total, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($pagado, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($debe, 2, ',', '.'),0,1,'R',true);/**/
}else{
		$pdf->Cell(48,5,substr($rowCliente['fullName'],0,50),0,0,'L',true);
		$pdf->Cell(43,5,substr($rowCliente['productoDes'],0,43),0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['giro'],0,0,'C',true);
		$pdf->Cell(17,5,number_format($total, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($pagado, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($debe, 2, ',', '.'),0,1,'R',true);/**/
}
	} while ($rowCliente = mysql_fetch_assoc($cliente));

	if(!isset($imp)){
		$pdf->Ln(7);
		$pdf->SetFont('arial','B',9); 
		$pdf->cell(35,5," ");
		$pdf->cell(17,5,"Total de Ventas: ".$acumTotArt);
		$pdf->cell(28,5," ");
		$pdf->cell(17,5,"Total Cobrado: ".$acumPag);
		$pdf->cell(25,5," ");
		$pdf->cell(17,5,"Total Deudas: ".$acumDebe);
	}
}

$pdf->AddPage();

//T�tulo del Reporte
$pdf->Cell(3,4,'',0,1);
$pdf->SetFont('times','B',12); 
$pdf->Cell(1,4,'Clientes'.$text,0,1);
$pdf->Cell(1,10, utf8_decode("Listado de Morosos Préstamos con"). $giros .utf8_decode(" o más cuotas vencidas"),0,1,'L');
$pdf->SetFont('Arial','',10);

//Connect to database 
mysql_select_db($database_tecno, $tecno);

$queryCliente = "SELECT *, COUNT(giro) as giro, CONCAT(nombres,' ', primerApellido) as fullName, CONCAT(telefono,' / ',celular) as telefonos FROM datospersonales, detallescliente, refpersonal, inflaboral WHERE status<>1 AND fechaV <'$today' AND datospersonales.id = detallescliente.id  AND datospersonales.id = refpersonal.id AND datospersonales.id=inflaboral.id AND inflaboral.ministerio= '$tpCli' AND detallescliente.tipo = 2 GROUP BY detallescliente.idArt HAVING COUNT(giro) >= '$giros' ORDER BY datospersonales.nombres ASC";
$cliente = mysql_query($queryCliente, $tecno) or die(mysql_error());
$rowCliente = mysql_fetch_assoc($cliente);
$totalRowsCliente = mysql_num_rows($cliente);

if ($totalRowsCliente >0){

	//Titulos Tabla
	$pdf->SetFont('arial','B',8); 
	$pdf->SetDrawColor(0);
	$pdf->SetFillColor(235,235,235);
	$pdf->SetTextColor(0);
	$pdf->SetLineWidth(0.15);
if(isset($imp)){	
	$pdf->Cell(48,6,'Nombres',1,0,'C',true);
	$pdf->Cell(38,6,'Tel�fonos',1,0,'C',true);
	$pdf->Cell(78,6,'Direcci�n',1,0,'C',true);
	$pdf->Cell(35,6,'Referencia',1,0,'C',true);	
	$pdf->Cell(20,6,'Tel�fono',1,0,'C',true);	
	$pdf->Cell(43,6,'Producto',1,0,'C',true);
    $pdf->Cell(20,6,'Cuotas Pend.',1,0,'C',true); 
	$pdf->Cell(17,6,'Monto Tot.',1,0,'C',true);
	$pdf->Cell(17,6,'Pagado',1,0,'C',true);
	$pdf->Cell(17,6,'Debe',1,1,'C',true);
}else{
	$pdf->Cell(48,6,'Nombres',1,0,'C',true);
	$pdf->Cell(43,6,'Producto',1,0,'C',true);
    $pdf->Cell(20,6,'Cuotas Pend.',1,0,'C',true); 
	$pdf->Cell(17,6,'Monto Tot.',1,0,'C',true);
	$pdf->Cell(17,6,'Pagado',1,0,'C',true);
	$pdf->Cell(17,6,'Debe',1,1,'C',true);
}	
	$pdf->Ln(1);

	$color=array("235,235,235","255,255,255");
	$conta_color=0;
	$acumTotArt=0;
	$acumPag=0;
	$acumDebe=0;
	
	//table/**/
	do {				
		$idArt=$rowCliente['idArt'];

		$queryPagos = "SELECT *, SUM(monto) as totalArt  FROM  detallescliente WHERE detallescliente.idArt = '$idArt'";
		$pagos = mysql_query($queryPagos, $tecno);
		$rowPagos = mysql_fetch_assoc($pagos);
		$totalRowsPagos = mysql_num_rows($pagos);
		//acumulamos
		$acumTotArt = round($rowPagos['totalArt'] + $acumTotArt,2);
		
		$totalArticulo = "SELECT Sum(cantidad) as pagado FROM pagos WHERE idArt = '$idArt' AND status = 0";
		$tot = mysql_query($totalArticulo, $tecno) or die(mysql_error());
		$row_tot = mysql_fetch_assoc($tot);
		$totalRows_tot = mysql_num_rows($tot);
		//acumulamos
		$acumPag = round($row_tot['pagado'] + $acumPag,2);

		$total = $rowPagos['totalArt'];
		$pagado = $row_tot['pagado'];
		$total = round($total,2);
		$pagado = round($pagado,2);
		$debe = ($total - $pagado);
		$acumDebe = $debe + $acumDebe;
		
		$conta_color++;
		$fill= $color[$conta_color%2];
		$pdf->SetFillColor($fill);
		$pdf->SetFont('arial','',8); 	
		
if(isset($imp)){		
		$pdf->Cell(48,5,substr($rowCliente['fullName'],0,50),0,0,'L',true);
		$pdf->Cell(38,5,$rowCliente['telefonos'],0,0,'L',true);	
		$pdf->Cell(78,5,$rowCliente['direccionHab'],0,0,'L',true);
		$pdf->Cell(35,5,$rowCliente['nombres1'],0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['telefono1'],0,0,'L',true);
		$pdf->Cell(43,5,substr($rowCliente['productoDes'],0,43),0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['giro'],0,0,'C',true);
		$pdf->Cell(17,5,number_format($total, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($pagado, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($debe, 2, ',', '.'),0,1,'R',true);/**/
}else{
		$pdf->Cell(48,5,substr($rowCliente['fullName'],0,50),0,0,'L',true);
		$pdf->Cell(43,5,substr($rowCliente['productoDes'],0,43),0,0,'L',true);
		$pdf->Cell(20,5,$rowCliente['giro'],0,0,'C',true);
		$pdf->Cell(17,5,number_format($total, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($pagado, 2, ',', '.'),0,0,'R',true);
		$pdf->Cell(17,5,number_format($debe, 2, ',', '.'),0,1,'R',true);/**/
}
	} while ($rowCliente = mysql_fetch_assoc($cliente));
	if(!isset($imp)){
		$pdf->Ln(7);
		$pdf->SetFont('arial','B',9); 
		$pdf->cell(35,5," ");
		$pdf->cell(17,5,"Total de Prestado: ".$acumTotArt);
		$pdf->cell(28,5," ");
		$pdf->cell(17,5,"Total Cobrado: ".$acumPag);
		$pdf->cell(25,5," ");
		$pdf->cell(17,5,"Total Deudas: ".$acumDebe);
	}
}
$pdf->Output();
?>